//
// Created by 86180 on 2025/11/7.
//
//数据库创建
#include "data.h"
//具体数据库功能实

int open_db(){
    int rc = sqlite3_open("library.db",&db);
    if(rc!=SQLITE_OK){
        fprintf(stderr,"open_db:创建数据库失败:%s\n", sqlite3_errmsg(db));
        return -1;
    }
    printf("open_db:创建数据库成功!\n");
    return 0;
}
int init_db() {
        // 创建管理员表（存储管理员账号密码）
    if (open_db() != 0) {
        fprintf(stderr, "init_db: 数据库连接为空\n");
        return -1;
    }

    // 依次创建各表，若任一表创建失败则返回错误
    if (create_admins() != 0) {
        return -1;
    }
    if (create_books() != 0) {
        return -1;
    }
    if (readers() != 0) {
        return -1;
    }
    if (create_borrows() != 0) {
        return -1;
    }

    printf("数据库初始化成功：所有表创建完成\n");

    return 0; // 成功返回0
}

//-- 管理员表
int create_admins(){
    char *sql_admin = "CREATE TABLE IF NOT EXISTS admins ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "username TEXT NOT NULL UNIQUE,"  // 用户名唯一
               "password TEXT NOT NULL);";       // 存储加密后的密码

    // 创建图书表
    char *errmsg = NULL;
    int rc;
    // 先创建管理员表
    rc = sqlite3_exec(db, sql_admin, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建管理员表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    return 0;
}
typedef struct {
    int id;
    char username[20];
    char password[10];
}Admin;
int insert_Admin(Admin *admin){char sql[256];
    sprintf(sql,"insert into admin(id,username,password) values('%s','%s','%d')",
            admin->id,admin->username,admin->password);
    char * errmsg;
    int rc = sqlite3_exec(db,sql,NULL,NULL,&errmsg);        //
    if(rc!=SQLITE_OK) {
        fprintf(stderr,"insert 插入数据出错了：%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("插入成功！\n");
    return 0;
}
//-- 图书表
int create_books(){
    char *sql_books = "CREATE TABLE IF NOT EXISTS books ("
                      "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "name TEXT NOT NULL,"
                      "author TEXT NOT NULL,"
                      "publisher TEXT NOT NULL,"
                      "quantity INTEGER NOT NULL);";
    char *errmsg = NULL;
    int rc;
    rc = sqlite3_exec(db, sql_books, NULL, NULL, &errmsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建图书表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;

    }
    printf("创建图书表成功！\n");
    return 0;

    }
//--读者表
int readers(){
    const char *sql = "CREATE TABLE IF NOT EXISTS readers ("
                      "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "name TEXT NOT NULL,"
                      "card_id TEXT NOT NULL UNIQUE,"
                      "phone TEXT,"
                      "register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);";
    char *errmsg = NULL;
    int rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建读者表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("读者表创建成功（或已存在）\n");
    return 0;
}
//-- 图书借阅记录表
int create_borrows(){
    const char *sql = "CREATE TABLE IF NOT EXISTS borrows ("
                      "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "book_id INTEGER NOT NULL,"
                      "reader_id INTEGER NOT NULL,"
                      "borrow_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                      "return_time TIMESTAMP,"
                      "FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,"
                      "FOREIGN KEY (reader_id) REFERENCES readers(id) ON DELETE CASCADE);";
    char *errmsg = NULL;
    int rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建借阅记录表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("借阅记录表创建成功（或已存在）\n");
    return 0;
}
//关闭数据库
int close_db(){
    if(db!=NULL){
        sqlite3_close(db);
        db=NULL;
    }
    printf("close_db:关闭数据库成功!\n");
    return 0;
}


